<?php
/***************************************************************************
 *							  admin_attach_cp.php
 *							  -------------------
 *	  begin				   : Saturday, Feb 09, 2002
 *	  copyright			   : (C) 2002 Meik Sievertsen
 *	  email				   : acyd.burn@gmx.de
 *
 ***************************************************************************/

/***************************************************************************
 *
 *	 This program is free software; you can redistribute it and/or modify
 *	 it under the terms of the GNU General Public License as published by
 *	 the Free Software Foundation; either version 2 of the License, or
 *	 (at your option) any later version.
 *
 ***************************************************************************/

if (!defined('ADMIN_PAGES')) { exit; }

if (!intval($attach_config['allow_ftp_upload'])) {
	$upload_dir = $attach_config['upload_dir'];
} else {
	$upload_dir = $attach_config['download_path'];
}

require_once(CORE_PATH.'phpBB/attach/functions_selects.php');
require_once(CORE_PATH.'phpBB/attach/functions_admin.php');

//
// Init Variables
//
$start = (int)$_GET->uint('start');

if (isset($_POST['order'])) {
	$sort_order = ($_POST['order'] == 'ASC') ? 'ASC' : 'DESC';
} else if (isset($_GET['order'])) {
	$sort_order = ($_GET['order'] == 'ASC') ? 'ASC' : 'DESC';
} else {
	$sort_order = '';
}

$mode = $_POST->txt('mode') ?: $_GET->txt('mode');

$view = $_POST->txt('view') ?: $_GET->txt('view');
if ('search' === $view && isset($_POST['search'])) {
	$view = 'attachments';
}

$uid = (int)$_GET->uint('uid');

//
// process modes based on view
//
if ('username' == $view)
{
	$mode_types = array(
		'username' => $lang['Sort_Username'],
		'attachments' => $lang['Sort_Attachments'],
		'filesize' => $lang['Sort_Size']
	);
	if (empty($mode))
	{
		$mode = 'attachments';
		$sort_order = 'DESC';
	}
}
else if ('attachments' == $view)
{
	$mode_types = array(
		'real_filename' => $lang['Sort_Filename'],
		'comment' => $lang['Sort_Comment'],
		'extension' => $lang['Sort_Extension'],
		'filesize' => $lang['Sort_Size'],
		'downloads' => $lang['Sort_Downloads'],
		'post_time' => $lang['Sort_Posttime']
	);
	if (empty($mode))
	{
		$mode = 'real_filename';
		$sort_order = 'ASC';
	}
}
else if ('search' == $view)
{
	$mode_types = array(
		'real_filename' => $lang['Sort_Filename'],
		'comment' => $lang['Sort_Comment'],
		'extension' => $lang['Sort_Extension'],
		'filesize' => $lang['Sort_Size'],
		'downloads' => $lang['Sort_Downloads'],
		'post_time' => $lang['Sort_Posttime']
	);
	$sort_order = 'DESC';
}
else
{
	$view = 'stats';
	$mode_types = array();
	$sort_order = '';
}

//
// Set Order
//
$order_by = '';

if ('username' == $view)
{
	switch ($mode)
	{
		case 'username':
			$order_by = 'u.username';
			break;
		case 'attachments':
			$order_by = 'total_attachments';
			break;
		case 'filesize':
			$order_by = 'total_size';
			break;
		default:
			$mode = 'attachments';
			$sort_order = 'DESC';
			$order_by = 'total_attachments';
			break;
	}
}
else if ('attachments' == $view)
{
	switch ($mode)
	{
		case 'filename':
			$order_by = 'a.real_filename';
			break;
		case 'comment':
			$order_by = 'a.comment';
			break;
		case 'extension':
			$order_by = 'a.extension';
			break;
		case 'filesize':
			$order_by = 'a.filesize';
			break;
		case 'downloads':
			$order_by = 'a.download_count';
			break;
		case 'post_time':
			$order_by = 'a.filetime';
			break;
		default:
			$mode = 'a.real_filename';
			$sort_order = 'ASC';
			$order_by = 'a.real_filename';
			break;
	}
}
if ($order_by) {
	$order_by =  " ORDER BY {$order_by} {$sort_order} LIMIT {$board_config['topics_per_page']} OFFSET {$start}";
}

//
// Set select fields
//

if ($mode_types)
{
	$attach_sort_fields = array();
	foreach ($mode_types as $k => $v) {
		$attach_sort_fields[] = array(
			'value' => $k,
			'label' => $v,
			'current' => ($k == $mode),
		);
	}
}

$delete_id_list = ( isset($_POST['delete_id_list']) ) ?	 $_POST['delete_id_list'] : array();

$confirm = isset($_POST['confirm']) ? TRUE : FALSE;

if ($confirm && count($delete_id_list) > 0) {
	$attachments = array();
	delete_attachment(-1, $delete_id_list);
} else if (isset($_POST['delete']) && (count($delete_id_list)) > 0 ) {
	//
	// Not confirmed, show confirmation message
	//
	$hidden_fields = '<input type="hidden" name="view" value="' . $view . '" />';
	$hidden_fields .= '<input type="hidden" name="mode" value="' . $mode . '" />';
	$hidden_fields .= '<input type="hidden" name="order" value="' . $sort_order . '" />';
	$hidden_fields .= '<input type="hidden" name="uid" value="' . $uid . '" />';
	$hidden_fields .= '<input type="hidden" name="start" value="' . $start . '" />';
	for($i = 0; $i < count($delete_id_list); $i++) {
		$hidden_fields .= '<input type="hidden" name="delete_id_list[]" value="' . $delete_id_list[$i] . '" />';
	}

	$template->assign_vars(array(
		'MESSAGE_TITLE' => $lang['Confirm'],
		'MESSAGE_TEXT' => $lang['Confirm_delete_attachments'],

		'L_YES' => $lang['Yes'],
		'L_NO' => $lang['No'],

		'S_CONFIRM_ACTION' => URL::admin('&amp;do=attach_cp'),
		'S_HIDDEN_FIELDS' => $hidden_fields)
	);
	$template->set_filenames(array('body' => 'confirm_body.html'));
	$template->display('body');
	CloseTable();
	return;
}

//
// Assign Default Template Vars
//
$template->assign_vars(array(
	'ATTACH_VERSION' => sprintf($lang['Attachment_version'], $attach_config['attach_version']),
	'U_VIEW_STATS' => URL::admin('&amp;do=attach_cp'),
	'U_VIEW_SEARCH' => URL::admin('&amp;do=attach_cp&amp;view=search'),
	'U_VIEW_USERNAME' => URL::admin('&amp;do=attach_cp&amp;view=username'),
	'U_VIEW_ATTACHMENTS' => URL::admin('&amp;do=attach_cp&amp;view=attachments'),
));

if ('stats' == $view)
{
	$template->set_filenames(array('body' => 'admin/forums/attach_cp_body'));

	list($number_of_attachments) = $db->uFetchRow('SELECT COUNT(*) FROM ' . ATTACHMENTS_DESC_TABLE);

	list($number_of_posts, $number_of_users, $number_of_topics) = $db->uFetchRow('SELECT
		COUNT(DISTINCT post_id),
		COUNT(DISTINCT user_id_1),
		COUNT(DISTINCT p.topic_id)
	FROM ' . ATTACHMENTS_TABLE . ' a
	LEFT JOIN ' . POSTS_TABLE . ' p USING (post_id)
	WHERE post_id > 0');

	list($number_of_pms) = $db->uFetchRow('SELECT COUNT(DISTINCT privmsgs_id) FROM ' . ATTACHMENTS_TABLE . ' WHERE privmsgs_id > 0');

	$template->assign_vars(array(
		'TOTAL_FILESIZE' => get_formatted_dirsize(),
		'ATTACH_QUOTA' => filesize_to_human(intval($attach_config['attachment_quota'])),
		'NUMBER_OF_ATTACHMENTS' => $number_of_attachments,
		'NUMBER_OF_POSTS' => $number_of_posts,
		'NUMBER_OF_PMS' => $number_of_pms,
		'NUMBER_OF_TOPICS' => $number_of_topics,
		'NUMBER_OF_USERS' => $number_of_users
	));
}

else if ('search' == $view)
{
	$result = $db->query("SELECT
		forum_id id,
		forum_name name
	FROM " . FORUMS_TABLE . "
	ORDER BY forum_name");
	if  (!$result->num_rows) {
		message_die(GENERAL_MESSAGE, $lang['No_searchable_forums']);
	}

	$template->set_filenames(array('body' => 'admin/forums/attach_cp_search'));

	$template->assign_vars(array(
		'search_forums' => $result,
		'search_categories' => $db->query("SELECT
				cat_id id,
				cat_title title
			FROM " . CATEGORIES_TABLE . "
			ORDER BY cat_title"),
		'attach_sort_fields' => $attach_sort_fields,
		'attach_sort_desc' => ($sort_order != 'ASC')
	));
}

else if ('username' == $view)
{
	$template->set_filenames(array('body' => 'admin/forums/attach_cp_user'));

	//
	// Get all Users with their respective total attachments amount
	//
	$members = $db->query("SELECT
		u.username,
		COUNT(attach_id) as total_attachments,
		ROUND(SUM(filesize)/1024, 2) as total_size,
		'".URL::admin('&amp;do=attach_cp&amp;view=attachments&amp;uid=')."' || a.user_id_1 as u_view_member
	FROM " . ATTACHMENTS_TABLE . " a
	INNER JOIN " . USERS_TABLE . " u ON (u.user_id = a.user_id_1)
	INNER JOIN " . ATTACHMENTS_DESC_TABLE . " d USING (attach_id)
	GROUP BY a.user_id_1, u.username " . $order_by);

	$template->assign_vars(array(
		'attach_sort_fields' => $attach_sort_fields,
		'attach_sort_desc' => ($sort_order != 'ASC'),
		'memberrow' => $members,
	));

	list($total_rows) = $db->uFetchRow("SELECT
		COUNT(DISTINCT user_id_1)
	FROM " . ATTACHMENTS_TABLE . " a
	INNER JOIN " . USERS_TABLE . " u ON (u.user_id = a.user_id_1)");
}

else if ('attachments' == $view)
{
	if (isset($_POST['submit_change'])) {
		if (isset($_POST['attach_list']) && is_array($_POST['attach_list'])) {
			$result = $db->query("SELECT attach_id, comment, download_count FROM " . ATTACHMENTS_DESC_TABLE
			. " WHERE attach_id IN (" . implode(',', array_keys($_POST['attach_list'])) . ")");
			while ($a = $result->fetch_assoc()) {
				if (isset($_POST['attach_list'][$a['attach_id']])) {
					$attachment = $_POST['attach_list'][$a['attach_id']];
					if ($a['comment'] != $attachment['comment']
					 || $a['download_count'] != $attachment['download_count']
					) {
						$db->query("UPDATE " . ATTACHMENTS_DESC_TABLE . "
						SET comment = " . $db->quote($attachment['comment']) . ", download_count = " . (int)$attachment['download_count'] . "
						WHERE attach_id = " . $a['attach_id']);
					}
				}
			}
		}
	}

	$template->set_filenames(array('body' => 'admin/forums/attach_cp_attachments'));
	$template->assign_vars(array(
		'attach_sort_fields' => $attach_sort_fields,
		'attach_sort_desc' => ($sort_order != 'ASC')
	));

	$total_rows = 0;

	if (isset($_POST['search'])) {
		//
		// we are called from search
		//
		$attachments = search_attachments($order_by, $total_rows);
	} else {
		//
		// Are we called from Username ?
		//
		if ($uid) {
			list($username) = $db->uFetchRow("SELECT username FROM " . USERS_TABLE . " WHERE user_id = " . $uid);

			$template->assign_vars(array(
				'L_STATISTICS_FOR_USER' => sprintf($lang['Statistics_for_user'], $username))
			);

			list($total_rows) = $db->uFetchRow("SELECT COUNT(DISTINCT attach_id) FROM " . ATTACHMENTS_TABLE . " WHERE user_id_1 = {$uid}");
			if (!$total_rows) {
				message_die(GENERAL_MESSAGE, 'For some reason no Attachments are assigned to the User "' . $username . '".');
			}

			$attachments = $db->query("SELECT
				a.*
			FROM " . ATTACHMENTS_TABLE . "
			INNER JOIN " . ATTACHMENTS_DESC_TABLE . " a USING (attach_id)
			WHERE user_id_1 = {$uid}
			GROUP BY attach_id {$order_by}");
		}
		else
		{
			list($total_rows) = $db->uFetchRow("SELECT COUNT(*) FROM " . ATTACHMENTS_DESC_TABLE);

			$attachments = $db->query("SELECT a.* FROM " . ATTACHMENTS_DESC_TABLE . " a " . $order_by);
		}
	}

	foreach ($attachments as $attachment)
	{
		//
		// Is the Attachment assigned to more than one post ?
		// If it's not assigned to any post, it's an private message thingy. ;)
		// Don't join topics table as it makes it very slow due to the
		// combination of posts and posts_archive table
		//
		$post_titles = array();
		$result = $db->query("SELECT
			a.post_id,
			p.topic_id,
			pa.topic_id,
			privmsgs_id
		FROM " . ATTACHMENTS_TABLE . " a
		LEFT JOIN " . POSTS_TABLE . " p ON (p.post_id=a.post_id)
		LEFT JOIN " . POSTS_ARCHIVE_TABLE . " pa ON (pa.post_id=a.post_id)
		WHERE attach_id = {$attachment['attach_id']}");
		if ($result->num_rows) {
			while ($row = $result->fetch_row()) {
				if ($row[3]) {
					$post_titles[] = $lang['Private_Message'];
				} else if (!$row[1] && !$row[2]) {
					$post_titles[] = 'Post/Topic removed';
				} else {
					list($post_title) = $db->uFetchRow("SELECT
						topic_title
					FROM " . TOPICS_TABLE . "
					WHERE topic_id = " . ($row[1]?:$row[2]));
					if (strlen($post_title) > 32) {
						$post_title = mb_substr($post_title, 0, 30) . '...';
					}
					$view_topic = URL::index('&amp;file=viewtopic&amp;' . POST_POST_URL . '=' . $row[0]) . '#' . $row[0];
					$post_titles[] = '<a href="' . $view_topic . '" class="gen" target="_blank">' . $post_title . '</a>';
				}
			}
		}

		$template->assign_block_vars('attachrow', array(
			'ID' => $attachment['attach_id'],
			'FILENAME' => $attachment['real_filename'],
			'COMMENT' => $attachment['comment'],
			'EXTENSION' => $attachment['extension'],
			'SIZE' => round(($attachment['filesize'] / 1024), 2),
			'DOWNLOAD_COUNT' => $attachment['download_count'],
			'POST_TIME' => create_date($board_config['default_dateformat'], $attachment['filetime']),
			'POST_TITLE' => implode('<br />', $post_titles),

			'DELETE_CHECKED' => in_array($attachment['attach_id'], $delete_id_list),
			'U_VIEW_ATTACHMENT' => URL::index('&amp;file=download&amp;id=' . $attachment['attach_id'])
		));
	}
}

//
// Generate Pagination
//
if ($view != 'stats' && $view != 'search' && $total_rows > $board_config['topics_per_page']) {
	$pagination = generate_admin_pagination('do=attach_cp&amp;view=' . $view . '&amp;mode=' . $mode . '&amp;order=' . $sort_order . '&amp;uid=' . $uid, $total_rows, $board_config['topics_per_page'], $start).'&nbsp;';
	$template->assign_vars(array(
		'PAGINATION' => $pagination,
		'PAGE_NUMBER' => sprintf($lang['Page_of'], ( floor( $start / $board_config['topics_per_page'] ) + 1 ), ceil( $total_rows / $board_config['topics_per_page'] )),
	));
}
